import pymysql
from datetime import datetime
import hashlib
import os


def write_report(schema_diff, data_diff, source_config, target_config, output_dir="diff_reports"):
    """生成结构化对比报告"""
    os.makedirs(output_dir, exist_ok=True)
    timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
    filepath = os.path.join(output_dir, f"diff_report_{timestamp}.txt")

    report = [
        "=" * 60,
        f"数据库对比报告 {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}",
        "=" * 60,
        f"源数据库: MySQL {source_config['host']}:{source_config['port']}:{source_config['database']} (5.7)",
        f"目标数据库: MySQL {target_config['host']}:{target_config['port']}:{target_config['database']} (8.0)",
        "-" * 60
    ]

    report.append("\n[结构差异]")
    report.extend(schema_diff if schema_diff else ["无结构差异"])

    report.append("\n[数据差异]")
    report.extend(data_diff if data_diff else ["无数据差异"])

    try:
        with open(filepath, 'w', encoding='utf-8') as f:
            f.write('\n'.join(report))
        return filepath
    except Exception as e:
        print(f"报告生成失败: {str(e)}")
        return None


class MySQLComparator:
    def __init__(self, source_config, target_config, batch_size=1000, output_dir="diff_reports"):
        self.source_config = source_config
        self.target_config = target_config
        self.batch_size = batch_size
        self.output_dir = output_dir
        self.schema_diff = []
        self.data_diff = []
        self.source_conn = None
        self.target_conn = None

    def _connect(self):
        """建立双数据库连接"""
        try:
            self.source_conn = pymysql.connect(
                **self.source_config,
                charset='utf8mb4',
                cursorclass=pymysql.cursors.SSCursor
            )
            self.target_conn = pymysql.connect(
                **self.target_config,
                charset='utf8mb4',
                cursorclass=pymysql.cursors.SSCursor
            )
        except Exception as e:
            raise ConnectionError(f"数据库连接失败: {str(e)}")

    def _get_table_metadata(self, conn):
        """获取MySQL表结构元数据"""
        with conn.cursor() as cursor:
            cursor.execute("""
                SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, 
                       IS_NULLABLE, COLUMN_DEFAULT, EXTRA
                FROM information_schema.columns 
                WHERE TABLE_SCHEMA = DATABASE()
                ORDER BY TABLE_NAME, ORDINAL_POSITION
            """)
            return cursor.fetchall()

    def _compare_schemas(self):
        """对比表结构"""
        source_meta = self._get_table_metadata(self.source_conn)
        target_meta = self._get_table_metadata(self.target_conn)

        # 转换数据结构
        source_dict = {}
        for row in source_meta:
            source_dict.setdefault(row, []).append(row[1:])

        target_dict = {}
        for row in target_meta:
            target_dict.setdefault(row, []).append(row[1:])

        # 检查表差异
        for table in set(source_dict) - set(target_dict):
            self.schema_diff.append(f"表缺失: {table} 在目标库不存在")
        for table in set(target_dict) - set(source_dict):
            self.schema_diff.append(f"表冗余: {table} 在源库不存在")

        # 对比共有表结构
        common_tables = set(source_dict) & set(target_dict)
        for table in common_tables:
            source_cols = {col: col for col in source_dict[table]}
            target_cols = {col: col for col in target_dict[table]}

            # 列差异检查
            for col in source_cols:
                if col not in target_cols:
                    self.schema_diff.append(f"表 {table}: 列缺失 - {col}")
                    continue

                # 列属性对比
                s_col = source_cols[col]
                t_col = target_cols[col]
                if s_col != t_col:
                    diff = [
                        f"表 {table}.{col} 结构不一致",
                        f"源: 类型={s_col}, 可空={s_col}, 默认值={s_col}, 额外={s_col}",
                        f"目: 类型={t_col}, 可空={t_col}, 默认值={t_col}, 额外={t_col}",
                        "-" * 50
                    ]
                    self.schema_diff.extend(diff)

            # 冗余列检查
            for col in set(target_cols) - set(source_cols):
                self.schema_diff.append(f"表 {table}: 列冗余 - {col}")

    def _get_primary_key(self, conn, table):
        """获取表的主键列"""
        with conn.cursor() as cursor:
            cursor.execute(f"""
                SELECT COLUMN_NAME 
                FROM information_schema.KEY_COLUMN_USAGE 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_NAME = '{table}' 
                AND CONSTRAINT_NAME = 'PRIMARY'
            """)
            result = cursor.fetchone()
            return result[0] if result else None

    def _batch_compare_data(self, table):
        """分页对比表数据"""
        pkey = self._get_primary_key(self.source_conn, table)
        offset = 0
        mismatch_count = 0

        while True:
            # 分页查询
            source_batch = self._fetch_batch(self.source_conn, table, pkey, offset)
            target_batch = self._fetch_batch(self.target_conn, table, pkey, offset)

            if not source_batch and not target_batch:
                break

            # 哈希快速对比
            if self._hash_batch(source_batch) != self._hash_batch(target_batch):
                # 逐行对比
                for idx, (s_row, t_row) in enumerate(zip(source_batch, target_batch)):
                    if s_row != t_row:
                        mismatch_count += 1
                        self.data_diff.append(
                            f"表 {table} 第{offset + idx + 1}行不一致\n"
                            f"源: {s_row}\n目: {t_row}\n"
                        )

            offset += self.batch_size

        if mismatch_count > 0:
            self.data_diff.append(f"表 {table} 共发现 {mismatch_count} 处数据不一致")

    def _fetch_batch(self, conn, table, pkey, offset):
        """获取分页数据"""
        with conn.cursor() as cursor:
            if pkey:
                query = f"SELECT * FROM {table} ORDER BY {pkey} LIMIT %s OFFSET %s"
            else:
                query = f"SELECT * FROM {table} LIMIT %s OFFSET %s"
            cursor.execute(query, (self.batch_size, offset))
            return cursor.fetchall()

    def _hash_batch(self, batch):
        """生成数据批次哈希"""
        return hashlib.md5(str(batch).encode()).hexdigest()

    def compare(self):
        """执行完整对比流程"""
        try:
            self._connect()
            start_time = datetime.now()

            print("开始结构对比...")
            self._compare_schemas()

            print("开始数据对比...")
            source_tables = [t for t in self._get_table_metadata(self.source_conn)]
            target_tables = [t for t in self._get_table_metadata(self.target_conn)]
            common_tables = set(source_tables) & set(target_tables)

            for table in common_tables:
                if table[0] == "tb_log":
                    continue
                print(f"正在对比表: {table[0]}")
                self._batch_compare_data(table[0])

            # 生成报告
            report_path = write_report(
                self.schema_diff,
                self.data_diff,
                self.source_config,
                self.target_config,
                self.output_dir
            )

            # 输出统计信息
            total_time = datetime.now() - start_time
            print(f"\n对比完成，总耗时: {total_time}")
            print(f"发现结构差异: {len(self.schema_diff)} 处")
            print(f"发现数据差异: {len(self.data_diff)} 处")
            print(f"报告文件路径: {os.path.abspath(report_path)}")

        finally:
            if self.source_conn:
                self.source_conn.close()
            if self.target_conn:
                self.target_conn.close()


if __name__ == "__main__":
    # 数据库配置
    SOURCE_CONFIG = {
        'host': '172.16.1.27',
        'user': 'root',
        'password': 'hmgf@600987',
        'database': 'wwi_mom',
        'port': 3306
    }

    TARGET_CONFIG = {
        'host': '172.16.1.27',
        'user': 'root',
        'password': 'hmgf@600987',
        'database': 'wwi_mom_test',
        'port': 3306
    }

    # 初始化比较器
    comparator = MySQLComparator(
        source_config=SOURCE_CONFIG,
        target_config=TARGET_CONFIG,
        batch_size=2000,
        output_dir="mysql_diff_reports"
    )

    # 执行对比
    comparator.compare()
